﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Crosland.Automotriz.Core;
using System.Data.SqlClient;
namespace Crosland.Automotriz.Data.Dao.Linq
{
    public class clsTD_MaterialDAO
    {
        private AutomotrizEntities ContextoDeObjs = new AutomotrizEntities();

        public bool Insertar(TD_MATERIAL Obj)
        {
            try
            {
                ContextoDeObjs.AddObject("TD_MATERIAL", Obj);
                ContextoDeObjs.SaveChanges();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        public bool Modificar(TD_MATERIAL obj)
        {
            try
            {
                var q = (from c in ContextoDeObjs.TD_MATERIAL
                         where c.ID_MATERIAL == obj.ID_MATERIAL
                         select c).FirstOrDefault();

                q.ID_MATERIAL = obj.ID_MATERIAL;
                q.COD_MATERIAL = obj.COD_MATERIAL;
                q.DES_MATERIAL = obj.DES_MATERIAL;
                q.ID_GRUPO_ARTICULO = obj.ID_GRUPO_ARTICULO;
                q.ID_SECTOR = obj.ID_SECTOR;
                q.ID_JERARQUIA = obj.ID_JERARQUIA;
                q.ID_MODELO = obj.ID_MODELO;
                q.PARTIDA = obj.PARTIDA;
                q.TIPO_ABC = obj.TIPO_ABC;
                q.COD_GRUPO_ARTICULO = obj.COD_GRUPO_ARTICULO;
                q.COD_SECTOR = obj.COD_SECTOR;
                q.COD_ARTICULO_SERV = obj.COD_ARTICULO_SERV;

                int resultado = ContextoDeObjs.SaveChanges();
                if (resultado == 1) return true;
                else return false;
            }
            catch (Exception)
            {
                return false;
            }
            //return true;
        }
        public bool Eliminar(TD_MATERIAL obj)
        {
            try
            {
                ContextoDeObjs.AttachTo("TD_MATERIAL", obj);
                ContextoDeObjs.DeleteObject(obj);
                ContextoDeObjs.SaveChanges();
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }
        public List<TD_MATERIAL> listar()
        {

            List<TD_MATERIAL> lista = new List<TD_MATERIAL>();
            try
            {
                var q = from c in ContextoDeObjs.TD_MATERIAL
                        orderby c.DES_MATERIAL
                        select c;

                lista = q.ToList();
            }
            catch (Exception)
            {
                return null;
            }
            return lista;
        }

        public List<TD_MATERIAL> listar2()
        {
            List<TD_MATERIAL> lista = new List<TD_MATERIAL>();
            try
            {
                var q = from c in ContextoDeObjs.TD_MATERIAL
                        select c;
                lista = q.ToList();
            }
            catch (Exception)
            {
                return null;
            }
            return lista;
        }

        public List<TD_MATERIAL> listar2(int intIdMaterial)
        {
            List<TD_MATERIAL> lista = new List<TD_MATERIAL>();
            try
            {
                var q = from c in ContextoDeObjs.TD_MATERIAL
                        where c.ID_MATERIAL == intIdMaterial
                        select c;
                lista = q.ToList();
            }
            catch (Exception)
            {
                lista = null;
            }
            return lista;
        }

        public List<TD_MATERIAL> listarKasaki(int intIdMaterial)
        {
            List<TD_MATERIAL> lista = new List<TD_MATERIAL>();
            try
            {
                var q = from c in ContextoDeObjs.TD_MATERIAL
                        join g in ContextoDeObjs.TD_GRUPO_ARTICULO on c.ID_GRUPO_ARTICULO equals g.ID_GRUPO_ARTICULO
                        where c.ID_MATERIAL == intIdMaterial && g.ID_GRUPO_ARTICULO == 22
                         && g.ID_GRUPO_ARTICULO == 31 && g.ID_GRUPO_ARTICULO == 32
                        select c;
                lista = q.ToList();
            }
            catch (Exception)
            {
                lista = null;
            }
            return lista;
        }



        public List<TD_MATERIAL> listarPorCodigo(String strCodigoMaterial)
        {
            List<TD_MATERIAL> lista = new List<TD_MATERIAL>();
            try
            {
                var q = from c in ContextoDeObjs.TD_MATERIAL
                        where c.COD_MATERIAL ==strCodigoMaterial
                        select c;
                lista = q.ToList();
            }
            catch (Exception)
            {
                lista = null;
            }
            return lista;
        }





        public IEnumerable<Object> ListarMaterial()
        {
            IEnumerable<Object> lista = new List<Object>();
            try
            {
                var q = from t in ContextoDeObjs.TD_MATERIAL
                        join t0 in ContextoDeObjs.TD_MODELO on t.TD_MODELO.ID_MODELO equals t0.ID_MODELO
                        join t1 in ContextoDeObjs.TD_MARCA on t0.TD_MARCA.ID_MARCA equals t1.ID_MARCA
                        join t2 in ContextoDeObjs.TD_SECTOR on t.TD_SECTOR.ID_SECTOR equals t2.ID_SECTOR
                        join t3 in ContextoDeObjs.TD_GRUPO_ARTICULO on t.TD_GRUPO_ARTICULO.ID_GRUPO_ARTICULO equals t3.ID_GRUPO_ARTICULO
                        join t4 in ContextoDeObjs.TD_JERARQUIA on t.TD_JERARQUIA.ID_JERARQUIA equals t4.ID_JERARQUIA
                        select new
                        {
                            t.ID_MATERIAL,
                            t.COD_MATERIAL,
                            t.DES_MATERIAL,
                            t3.DES_GRUPO_ARTICULO_2,
                            t2.DES_SECTOR,
                            t4.DES_JERARQUIA,
                            t1.DES_MARCA,
                            t0.DES_MODELO
                        };
                lista = q.ToList();
            }
            catch (Exception)
            {
                lista = null;
            }
            return lista;
        }

        public IEnumerable<Object> Buscar_Art_SusTop(string codMaeterial, int nu_rec)
        {
            try
            {
                var p = new object[] { new SqlParameter("@VARIABLE", codMaeterial) };
                var resultado = ContextoDeObjs.ExecuteStoreQuery<T_ARTICULOS_RELACIONADOS>("exec USP_BUSCAR_ARTICULOS @VARIABLE", p).AsQueryable().ToList().Take(nu_rec);
                return resultado;
            }
            catch (Exception)
            {
                return null;
            }
        
        }


        public IEnumerable<Object> Buscar_Art_SusTopKawasaki(string codMaeterial, int nu_rec)
        {
            try
            {
                var p = new object[] { new SqlParameter("@VARIABLE", codMaeterial) };
                var resultado = ContextoDeObjs.ExecuteStoreQuery<T_ARTICULOS_RELACIONADOS>("exec USP_BUSCAR_ARTICULOS_KAWASAKI @VARIABLE", p).AsQueryable().ToList().Take(nu_rec);
                return resultado;
            }
            catch (Exception)
            {
                return null;
            }

        }





        public int RetorIDMaterial(string COD_MATERIAL)
        {
            try
            {
                var queryText = String.Format("SELECT tm.ID_MATERIAL FROM TD_MATERIAL tm WHERE tm.COD_MATERIAL='{0}'", COD_MATERIAL);
                var lisr = ContextoDeObjs.ExecuteStoreQuery<int>(queryText).ToList();
                return lisr.FirstOrDefault();
            }
            catch (Exception)
            {
                return 0;
            }
        }


        public List<T_MATERIAL_TIPO> ListaMaterialxId(int id_material)
        {
            List<T_MATERIAL_TIPO> list = new List<T_MATERIAL_TIPO>();
            try
            {

                var queryText = String.Format("SELECT tm.TIPO_ABC,tm.TIPO_ABC_CAN FROM TD_MATERIAL tm  WHERE tm.ID_MATERIAL={0}", id_material);
                var q = ContextoDeObjs.ExecuteStoreQuery<Crosland.Automotriz.Core.T_MATERIAL_TIPO>(queryText);
                list = q.ToList();
            }
            catch (Exception ex)
            {

                throw ex;
            }

            return list;
        }


 

        public bool Insert_T_MATERIAL_POR_ASIGNAR(string cod_material, string des_material)
        {
            try
            {
                ContextoDeObjs.ExecuteStoreCommand("exec USP_INSERTAR_MATERIAL_POR_ASIGNAR @COD_MATERIAL={0},@DES_MATERIAL={1}", cod_material, des_material);
                return true;
            }
            catch (Exception)
            {
                return false;

            }
        }

        public Boolean VerificarCodigo(String strCodMaterial)
        {
            Boolean boolValidacion = false;
            try
            {
                var q = from t in
                            (from t in ContextoDeObjs.TD_MATERIAL
                             where
                               t.COD_MATERIAL.ToUpper() == strCodMaterial.ToUpper()
                             select new
                             {
                                 Dummy = "x"
                             })
                        group t by new { t.Dummy } into g
                        select new
                        {
                            cantidad = (System.Int64?)(g.Count())
                        };
                int cant = 0;
                foreach (var item in q.ToList())
                {
                    cant = Convert.ToInt32(item.cantidad.ToString());
                }
                boolValidacion = (cant == 0) ? true : false;
            }
            catch (Exception)
            {
                boolValidacion = false;
            }
            return boolValidacion;
        }

        public IEnumerable<Object> BuscarMaterial(String strCodMaterial, String strNroRuedas, int? intIdMarca, int? intIdModelo, int? intIdSector, int? intidGrupoArticulo)
        {
            IEnumerable<Object> listaMaterial = new List<Object>();
            try
            {

                var q = from t in ContextoDeObjs.TD_MATERIAL
                        join t0 in ContextoDeObjs.TD_MODELO on t.TD_MODELO.ID_MODELO equals t0.ID_MODELO
                        join t1 in ContextoDeObjs.TD_MARCA on t0.TD_MARCA.ID_MARCA equals t1.ID_MARCA
                        join t2 in ContextoDeObjs.TD_SECTOR on t.TD_SECTOR.ID_SECTOR equals t2.ID_SECTOR
                        join t3 in ContextoDeObjs.TD_GRUPO_ARTICULO on t.TD_GRUPO_ARTICULO.ID_GRUPO_ARTICULO equals t3.ID_GRUPO_ARTICULO
                        join t4 in ContextoDeObjs.TD_JERARQUIA on t.TD_JERARQUIA.ID_JERARQUIA equals t4.ID_JERARQUIA
                        where 1 == 1
                        select new
                        {
                            t.ID_MATERIAL,
                            t.COD_MATERIAL,
                            t.DES_MATERIAL,
                            t3.DES_GRUPO_ARTICULO_2,
                            t3.DES_GRUPO_ARTICULO,
                            t2.DES_SECTOR,
                            t4.DES_JERARQUIA,
                            t1.DES_MARCA,
                            t0.DES_MODELO,
                            t1.ID_MARCA,
                            t0.TIP_NUM_RUEDA,
                            t2.ID_SECTOR,
                            t3.ID_GRUPO_ARTICULO,
                            t0.ID_MODELO,
                            t.FEC_CREACION
                        };

                if (strCodMaterial != "") q = q.Where(f1 => f1.COD_MATERIAL.Contains(strCodMaterial));
                if (strNroRuedas != "") q = q.Where(f1 => f1.TIP_NUM_RUEDA == strNroRuedas);
                if (intIdMarca != null) q = q.Where(f1 => f1.ID_MARCA == intIdMarca);
                if (intIdModelo != null) q = q.Where(f1 => f1.ID_MODELO == intIdModelo);
                if (intIdSector != null) q = q.Where(f1 => f1.ID_SECTOR == intIdSector);
                if (intidGrupoArticulo != null) q = q.Where(f1 => f1.ID_GRUPO_ARTICULO == intidGrupoArticulo);

                listaMaterial = q.OrderBy(x => x.DES_MATERIAL).ToList();
            }
            catch (Exception)
            {
                listaMaterial = null;
            }
            return listaMaterial;
        }

        public IEnumerable<Object> listaRuedaFiltro()
        {
            IEnumerable<Object> lista = new List<Object>();
            try
            {
                var q = (from tm in ContextoDeObjs.TD_MATERIAL
                         join tm2 in ContextoDeObjs.TD_MODELO on tm.TD_MODELO.ID_MODELO equals tm2.ID_MODELO
                         where tm2.IND_BAJA == 0
                         orderby tm2.TIP_NUM_RUEDA
                         select new
                         {
                             des = tm2.TIP_NUM_RUEDA,
                             cod = tm2.TIP_NUM_RUEDA
                         }).Distinct();
                lista = q.OrderBy(x => x.des).ToList();
            }
            catch (Exception)
            {
                lista = null;
            }
            return lista;
        }

        public IEnumerable<Object> listaMarcaFiltro()
        {
            IEnumerable<Object> lista = new List<Object>();
              var q = (from tm in ContextoDeObjs.TD_MATERIAL
                         join tm2 in ContextoDeObjs.TD_MODELO on tm.TD_MODELO.ID_MODELO equals tm2.ID_MODELO
                         where tm2.IND_BAJA == 0
                         select new
                         {
                             des = tm2.DES_MARCA,
                             id = (Int32?)tm2.TD_MARCA.ID_MARCA
                         }).Distinct();
                lista = q.OrderBy(x => x.des).ToList();          
            
            return lista;
        }

        public IEnumerable<Object> listaModeloFiltro()
        {
            IEnumerable<Object> lista = new List<Object>();
            try
            {
                var q = (from tm in ContextoDeObjs.TD_MATERIAL
                         join tm2 in ContextoDeObjs.TD_MODELO on tm.TD_MODELO.ID_MODELO equals tm2.ID_MODELO
                         where tm2.IND_BAJA == 0
                         orderby tm2.DES_MODELO
                         select new
                         {
                             des = tm2.DES_MODELO,
                             id = (Int32?)tm2.ID_MODELO
                         }).Distinct();
                lista = q.OrderBy(x => x.des).ToList();
            }
            catch (Exception)
            {
                lista = null;
            }
            return lista;
        }

        public IEnumerable<Object> listaMaterialxAsignar()
        {
            IEnumerable<Object> lista = new List<Object>();         
                var q = from t in ContextoDeObjs.TD_MATERIAL
                        join t0 in ContextoDeObjs.TD_MODELO on t.TD_MODELO.ID_MODELO equals t0.ID_MODELO
                        join t1 in ContextoDeObjs.TD_MARCA on t0.TD_MARCA.ID_MARCA equals t1.ID_MARCA
                        join t2 in ContextoDeObjs.TD_SECTOR on t.TD_SECTOR.ID_SECTOR equals t2.ID_SECTOR
                        join t3 in ContextoDeObjs.TD_GRUPO_ARTICULO on t.TD_GRUPO_ARTICULO.ID_GRUPO_ARTICULO equals t3.ID_GRUPO_ARTICULO
                        join t4 in ContextoDeObjs.TD_JERARQUIA on t.TD_JERARQUIA.ID_JERARQUIA equals t4.ID_JERARQUIA
                        where (from c in ContextoDeObjs.T_MATERIAL_POR_ASIGNAR where c.IND_ASIGNADO == false select c.COD_MATERIAL).Contains(t.COD_MATERIAL)
                        select new
                        {
                            t.ID_MATERIAL,
                            t.COD_MATERIAL,
                            t.DES_MATERIAL,
                            t.FEC_CREACION,
                            t3.DES_GRUPO_ARTICULO_2,
                            t3.DES_GRUPO_ARTICULO,
                            t2.DES_SECTOR,
                            t4.DES_JERARQUIA,
                            t1.DES_MARCA,
                            t0.DES_MODELO,
                            IND_ASIGNADO = 0
                        };
                lista = q.OrderByDescending(x => x.FEC_CREACION).ToList();            
            return lista;
        }
        
        public IEnumerable<Object> Listar(String strCodMaterial, String strDesMaterial, int? intIdSector, int? intcantLimite)
        {
            IEnumerable<Object> lista = new List<Object>();           
                var materiales = (from t in ContextoDeObjs.TD_MATERIAL
                                  join t0 in ContextoDeObjs.TD_FOB on t.ID_MATERIAL equals t0.ID_MATERIAL into Materiales
                                  join t1 in ContextoDeObjs.TD_MODELO on t.ID_MODELO equals t1.ID_MODELO into Modelos
                                  join t2 in ContextoDeObjs.TD_GRUPO_ARTICULO on t.ID_GRUPO_ARTICULO equals t2.ID_GRUPO_ARTICULO into Grupo
                                  from mat in Materiales.DefaultIfEmpty()
                                  select new { t.ID_MATERIAL, t.COD_MATERIAL, t.DES_MATERIAL, FOB = mat.FOB ?? 0, t.ID_SECTOR, t.TD_MODELO.DES_MODELO, t.TD_GRUPO_ARTICULO.DES_GRUPO_ARTICULO });

                var q = (from mat in materiales.ToList()
                         join sec in ContextoDeObjs.TD_SECTOR on mat.ID_SECTOR equals sec.ID_SECTOR
                         where 1 == 1
                         select new
                          {
                              mat.ID_MATERIAL,
                              mat.COD_MATERIAL,
                              mat.DES_MATERIAL,
                              mat.FOB,
                              DES_SECTOR = sec.DES_SECTOR.Trim(),
                              sec.ID_SECTOR,
                              mat.DES_MODELO,
                              mat.DES_GRUPO_ARTICULO,
                          }
                       );

                if (strCodMaterial != String.Empty) q = q.Where(f1 => f1.COD_MATERIAL.ToUpper().Contains(strCodMaterial.ToUpper()));
                if (strDesMaterial.Contains("%"))
                {
                    String[] strDesMateriales = strDesMaterial.Split('%');
                    q = q.Where(f1 => strDesMateriales.All(y => f1.DES_MATERIAL.ToUpper().Contains(y.ToUpper())));
                }
                else
                {
                    if (strDesMaterial != String.Empty) q = q.Where(f1 => f1.DES_MATERIAL.ToUpper().Contains(strDesMaterial.ToUpper()));
                }
                if (intIdSector != null) q = q.Where(f1 => f1.ID_SECTOR == intIdSector);

                lista = q.ToList().Take(Convert.ToInt32(intcantLimite));           
            return lista;
        }

        public IEnumerable<Object> ListarMaterial(String strCodMaterial)
        {
            IEnumerable<Object> lista = new List<Object>();
            try
            {
                var q = from t in ContextoDeObjs.TD_MATERIAL
                        join t0 in ContextoDeObjs.TD_FOB on t.ID_MATERIAL equals t0.ID_MATERIAL into Materiales
                        from mat in Materiales.DefaultIfEmpty()
                        select new
                        {
                            t.ID_MATERIAL,
                            t.COD_MATERIAL,
                            t.DES_MATERIAL,
                            FOB = mat.FOB ?? 0,
                            DES_SECTOR = String.Empty
                        };

                if (strCodMaterial != String.Empty) q = q.Where(f1 => f1.COD_MATERIAL == strCodMaterial);
                lista = q.ToList();
            }
            catch (Exception)
            {
                lista = null;
                throw;
            }

            return lista;

        }
        
        public IEnumerable<Object> ListaMaterialPorAsignar() {
            IEnumerable<Object> lista = new List<Object>();

            var q = from m in ContextoDeObjs.T_MATERIAL_POR_ASIGNAR
                    join g in ContextoDeObjs.TD_GRUPO_ARTICULO on m.ID_GRUPO_ARTICULO equals g.ID_GRUPO_ARTICULO
                    join j in ContextoDeObjs.TD_JERARQUIA on m.ID_JERARQUIA equals j.ID_JERARQUIA
                    join s in ContextoDeObjs.TD_SECTOR on m.ID_SECTOR equals s.ID_SECTOR
                    join t0 in ContextoDeObjs.TD_MATERIAL on m.COD_MATERIAL equals t0.COD_MATERIAL into Materiales
                    join mo in ContextoDeObjs.TD_MODELO on m.ID_MODELO equals mo.ID_MODELO
                    from mat in Materiales.DefaultIfEmpty()
                    where m.IND_ASIGNADO==false && mat.COD_MATERIAL==null
                    select new
                    {
                        m.ID_MATERIAL,
                        m.COD_MATERIAL,
                        m.DES_MATERIAL_IMP,
                        m.ID_GRUPO_ARTICULO,
                        g.COD_GRUPO_ARTICULO,
                        m.ID_JERARQUIA,
                        j.COD_JERARQUIA,
                        m.ID_MODELO,
                        m.ID_SECTOR,
                        g.DES_GRUPO_ARTICULO,
                        j.DES_JERARQUIA,
                        s.DES_SECTOR,
                        s.COD_SECTOR,
                        mo.DES_MODELO
                    };

            lista = q.ToList();

            return lista;
        
        }
        

        public List<TD_MATERIAL_EN> ListaMaterialStock_mas_Transito(String strFiltro,int  intTotalRegistros) {


            var q = String.Format("SELECT f.ID_MATERIAL, f.COD_MATERIAL, f.DES_MATERIAL, f.MODELO, f.GRUPO_ARTICULOS,f.STOCK_ACTUAL, f.CANT_TRANSITO,f.CUP_S,f.CUP_D,FOB=ISNULL(f.FOB,0)  from dbo.FU_LISTAR_MATERIALES({0},'{1}',{2}) AS f",2, strFiltro,intTotalRegistros);
            var lista= ContextoDeObjs.ExecuteStoreQuery<TD_MATERIAL_EN>(q).ToList();
            return lista;
        
        
        }

    }
}
